package com.example.myapplication;

import android.database.Cursor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

public class RemoteDataBaseHelper implements RemoteDatabaseCallback {
  Connection connection;


  RemoteDataBaseHelper() {
    try {
      //connection = remoteDataBaseConnectTest.getConnection();

      //connection = RemoteDataBaseConnector.GetConnection();
      RemoteDataBaseConnector remoteDataBaseConnector = new RemoteDataBaseConnector(this);
      remoteDataBaseConnector.execute();

    } catch (Exception e) {
      System.out.println("数据库链接失败");
      throw new RuntimeException(e);
    }
    //try {
    //  connection = RemoteDataBaseConnectTest.GetConnection();
    //} catch (Exception e) {
    //  System.out.println("没能连接上数据库");
    //  throw new RuntimeException(e);
    //}
  }

  @Override public void onConnectionObtained(Connection connection) {
    this.connection = connection;

  }

  public int UpLoadNoteData(ArrayList<Note> notes) {
    if (connection == null) {
      return -1;
    }
    int size = notes.size();
    if (size == 0) {
      return 0;
    }



    //先清空表格
    //try {
    //  String truncateQuery = "TRUNCATE TABLE " + RemoteDataBaseConnector.NOTE_TABLE;
    //  Statement statement = connection.createStatement();
    //  statement.execute(truncateQuery);
    //
    //} catch (SQLException e) {
    //  System.out.println("删除表格失败");
    //  throw new RuntimeException(e);
    //}
    PreparedStatement preparedStatement = null;

    try {

      //Statement statement = connection.createStatement();
      //取消自动提交
      connection.setAutoCommit(false);

      String insertQuery =
          "INSERT INTO "+RemoteDataBaseConnector.NOTE_TABLE+" (noteID, authorName, authorAccount, title, createtime, updatetime, content,favour) VALUES (?, ?, ?, ?, ?, ?, ?,?)";
      preparedStatement = connection.prepareStatement(insertQuery);

      for (Note note : notes) {
        preparedStatement.setInt(1, note.getNoteId());
        preparedStatement.setString(2, note.getAuthorName());
        preparedStatement.setString(3, note.getAuthorAccount());
        preparedStatement.setString(4, note.getNoteTitle());
        Timestamp createTimestamp = new Timestamp(note.getCreateTime().getTime());
        Timestamp updateTimestamp = new Timestamp(note.getUpdateTime().getTime());

        preparedStatement.setTimestamp(5, createTimestamp);
        preparedStatement.setTimestamp(6, updateTimestamp);
        preparedStatement.setString(7, note.getNoteContent());
        int flag = note.isFavour() ? 1 : 0;
        preparedStatement.setInt(8, flag);
      }
      connection.commit();//提交事务


    } catch (Exception e) {
      e.printStackTrace();
      try {
        connection.rollback();

      } catch (SQLException ex) {
        ex.printStackTrace();
      }
    }finally {
      if (preparedStatement != null) {
        try {
          preparedStatement.close();
        } catch (SQLException e) {
          e.printStackTrace();

        }
      }
    }
    // 结束事务和关闭链接
    try {
      connection.setAutoCommit(true);

    } catch (SQLException e) {
      throw new RuntimeException(e);
    }

    return 1;
  }

  public void Close() {
    try {
      connection.close();
    } catch (SQLException e) {
      System.out.println("关闭数据库失败");

      throw new RuntimeException(e);
    }
  }
  public void Open() {
    try {
      connection = RemoteDataBaseConnector.GetConnection();
    } catch (Exception e) {
      System.out.println("链接数据库失败");
      throw new RuntimeException(e);
    }
  }

}
